/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.suli.location.repository;

import me.zhengjie.suli.location.domain.Location;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
* @website https://eladmin.vip
* @author WYH
* @date 2024-02-05
**/
public interface LocationRepository extends JpaRepository<Location, Long>, JpaSpecificationExecutor<Location> {
    @Query(value="select top(?1) * from location where status = 3 and type = ?2", nativeQuery = true)
    List<Location> getLocationByQty(int qty, int type);

    /** 根据取料箱的数量和类型选择空料箱库位 */
//    @Query(value = "select top(?1) * from location where status = 3 and type = ?2 and [layer] <=4 order by [row] asc, [layer] asc", nativeQuery = true)
    /** Allen Gao 2024-06-04 根据会议内容，把取箱库位打散 */
    @Query(value = "select top(?1) * from location where status = 3 and type = ?2 and [layer] <=4 order by NEWID()", nativeQuery = true)
    List<Location> getLocationsByQtyAndTypeWithLayerLessThen4(int qty, int type);

    /** 取4层以上的空料箱 */
//    @Query(value = "select top(?1) * from location where status = 3 and type = ?2 order by [row] asc, [layer] desc ", nativeQuery = true)

    /** Allen Gao 2024-06-04 根据会议内容， */
    @Query(value = "select top(?1) * from location where status = 3 and type = ?2 order by NEWID() ", nativeQuery = true)
    List<Location> getLocationsByQtyAndTypeOrderByLayerDesc(int qty, int type);

    @Query(value = "select top(1) * from location where location_code = ?1", nativeQuery = true)
    Location getLocationsByLocationCode(String locationCode);

    /** 入库库位推荐  */
    @Query(value = "select top(?1) * from location where status = 0 and type = ?2 AND layer <= 4 order by [row] asc, [column] asc, [layer] asc", nativeQuery = true)
    List<Location> getEmptyLocationsByQtyAndTypeWithLayerLessThen4(int qty, int type);

    @Query(value = "select * from location where tray_code like '%B%' and (location_code like '%12_1' or location_code like '%12_2') AND [column] = 2  and location_code not in ('HW-002-007-12_1', 'HW-002-006-12_1','HW-002-002-12_1', 'HW-002-002-12_2', 'HW-002-004-12_2', 'HW-002-010-12_2', 'HW-002-003-12_2', 'HW-002-006-12_2', 'HW-002-012-12_2', 'HW-002-001-12_2', 'HW-002-008-12_2', 'HW-002-009-12_2', 'HW-002-004-12_1', 'HW-002-008-12_1', 'HW-002-003-12_1', 'HW-002-001-12_1', 'HW-002-011-12_1')", nativeQuery = true)
    List<Location> findByAAA();
    /** 入库库位推荐  */
    @Query(value = "select top(?1) * from location where status = 0 and type = ?2 AND layer <= 4 order by [row] asc, [column] desc, [layer] asc", nativeQuery = true)
    List<Location> getEmptyLocationsByQtyAndTypeWithLayerLessThen4AndColumn(int qty, int type);

    /** 取4层以上的空料箱 */
    @Query(value = "select top(?1) * from location where status = 0 and type = ?2 order by [row] asc, [column] asc, [layer] desc ", nativeQuery = true)
    List<Location> getEmptyLocationsByQtyAndTypeOrderByLayerDesc(int qty, int type);

    @Query(value = "SELECT * FROM (select * from location where location_code like '%CW-002%') as locationS where locationS.location_code like '%_2' ", nativeQuery = true)
    List<Location> getLocationByCw();

    /** 取4层以上的空料箱,倒 */
    @Query(value = "select top(?1) * from location where status = 0 and type = ?2 order by [row] asc, [column] desc, [layer] desc ", nativeQuery = true)
    List<Location> getEmptyLocationsByQtyAndTypeOrderByLayerDescAndColumn(int qty, int type);

    @Query(value = "select  * from location where status = 3 and type = ?2 and [layer] <=2 order by [row] asc, [column] asc, [layer] asc", nativeQuery = true)
    Location getLocationByIdIs(int qty, int type);
    /** 找没料箱占用的库位 */
    @Query(value = "select  * from location where status = 0 ", nativeQuery = true)
    Location findLocationByStatus(int type);

    @Query(value = "select  * from location where location_code = ?1 ", nativeQuery = true)
    Location findLocationByLocationCode(String locationCode);

    @Query(value = "select  * from location where status = ?1 AND location_code like ?2  ORDER BY row ", nativeQuery = true)
    List<Location> findByStatus(String status,String kuQu);

    @Query(value = "select  * from location where status = ?1 AND location_code like ?2  ORDER BY [column] DESC ", nativeQuery = true)
    List<Location> findByStatusTwo(String status,String kuQu);
    @Query(value = "select  * from location where status = ?1 AND location_code like ?2  ORDER BY [column]  ", nativeQuery = true)
    List<Location> findByStatusOne(String status,String kuQu);

    @Query(value = "select  * from location where status = ?1 AND location_code like ?2  ORDER BY row ", nativeQuery = true)
    List<Location> getByStatus(String status,String locationCode);

    @Query(value = "select  top(1) * from location where tray_code = ?1  ", nativeQuery = true)
    Location findByTrayCode(String trayCode);

    @Query(value = "select  * from location where tray_code in (?1)  ", nativeQuery = true)
    List<Location> findByTrayCodes(List<String> trayCode);

    @Query(value = "update  location set  tray_code = '' AND status = 0  ", nativeQuery = true)
    Integer check();

    @Query(value = "select  * from location where status = ?1 AND location_code like '%CW%'    ORDER BY row ", nativeQuery = true)
    List<Location> findByStatusAndOut(String status,String column);

    /** 库位统计图 */
    @Query(value = "select location.warehouse_code, location.location_code, " +
            "concat (concat (ISNULL( inventory.material_name, '' ), ' ', ISNULL ( inventory.available_quantity, 0 )), ' ', ISNULL ( inventory.el_unit, '' )) AS 'material', " +
            "[status], [layer], [column], [row], [body_position], " +
            "( select MAX([row]) from location where warehouse_code LIKE ?1 ) as 'max_row', " +
            "( select MAX([column]) from location where warehouse_code LIKE ?1 ) as 'max_col', " +
            "( select MAX([layer]) from location where warehouse_code LIKE ?1 ) as 'max_layer' " +
            "from location LEFT JOIN inventory ON location.location_code = inventory.location_code " +
            "where location.warehouse_code LIKE ?1 and [layer] = ?2 " +
            "order by [column] asc, [row] asc, body_position asc ", nativeQuery = true)
    List<Map<String, Object>> getLocationUsage(String warehouseCode, int row);

}